import os
import pandas as pd

dir = 'xls'
target = 'split'

if __name__ == '__main__':
    df = pd.read_excel(dir + "/" + os.listdir(dir)[0], sheet_name=1)

    titles = []
    line1 = df.loc[:1]
    ts = df.loc[2]
    for t in ts:
        if(len(str(t)) > 0 and str(t) != "nan"):
            titles.append(str(t))
        else:
            titles.append("")


    areas = {}

    for i in range(1000):
        try:
            line = df.loc[i+4]
            area = str(line[1])
            datas = []
            if area in areas:
                datas = areas[area]
            datas.append(line)
            areas[area] = datas
            # print(datas)
        except:
            break

    for key in areas.keys():
        print(key)
        print(len(areas[key]))

        datas = areas[key]
        for title in titles:
            print(title)

        res = []
        for data in datas:
            line = []
            for cell in data:
                if (len(str(cell)) > 0 and str(cell) != "nan"):
                    line.append(str(cell))
                else:
                    line.append("")
            res.append(line)

        writer = pd.ExcelWriter(target+"/"+key+".xlsx")
        df = pd.DataFrame(res)
        df.columns = titles
        df.to_excel(writer, sheet_name=key, index=False, startrow=0)
        try:
            writer.save()
        except:
            pass

